IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('PaintingSearch','P'))
    DROP PROCEDURE PaintingSearch
GO
/*
 * Designer:     Kevin
 * Description:    
 * Created:      03/10/2011
 * History:
 * =============================================================================
 * Author      DateTime        Alter Description
 * =============================================================================
 */

CREATE PROCEDURE PaintingSearch
(
	@HandEnable bit = NULL,
	@PrinterEnable bit = NULL,
	@SubjectId int = NULL,
	@AuthorshipId int = NULL,
	@Frames int = NULL,
	@Keyword Nvarchar(100) = NULL,
	@TagId int = NULL,
	@PageIndex int,
	@PageSize int,
	@RowCount int output
)
AS
BEGIN
	declare @keyword_pattern Nvarchar(100)
	set @keyword_pattern= '%'+@Keyword +'%'
	
	SELECT
		ROW_NUMBER() over(order by p.painting_id) AS RowId,
		p.painting_id as PaintingId,
		p.title AS Title,
		p.painting_number AS PaintingNumber,
		p.image_url AS ImageUrl,
		p.hand_enable AS HandEnable,
		p.printer_enable AS PrinterEnable
	INTO #TempPainting		
	FROM
	Painting p
	left join painting_subject ps on p.painting_id=ps.painting_id  
	left join painting_authorship pa on p.painting_id=pa.painting_id 
	left join painting_tag pt on p.painting_id=pt.painting_id 
	left join subject s on ps.subject_id=s.subject_id
	left join authorship a on pa.authorship_id=a.authorship_id
	left join tag t on pt.tag_id=t.tag_id
	where 
		(@Keyword is null or @Keyword = '' 
		or p.title like @keyword_pattern
		or p.description like @keyword_pattern
		or s.value like @keyword_pattern
		or a.value like @keyword_pattern
		or t.value like @keyword_pattern)
		and (@PrinterEnable is null or p.printer_enable=@PrinterEnable)
		and (@HandEnable is null or @HandEnable=0 or p.hand_enable=@HandEnable)
		and (@SubjectId is null or @SubjectId=0 or ps.subject_id=@SubjectId)
		and (@AuthorshipId is null or @AuthorshipId=0 or pa.authorship_id=@AuthorshipId)
		and (@TagId is null or @TagId=0 or pt.tag_id=@TagId)
	group by p.painting_id, p.title, p.painting_number, p.image_url, p.hand_enable, p.printer_enable
		
	select @RowCount=@@RowCount
	
	declare @PageStart int,
			@PageEnd int

	select @PageStart= (@PageIndex-1)*@PageSize, @PageEnd=@PageStart+@PageSize	
	print @PageStart
	print @PageEnd	
	select * from
		#TempPainting	
	where
		RowId>@PageStart and RowId<=@PageEnd					
	DROP TABLE #TempPainting	
END
GO